{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# BigQuery ML Semi-supervised Self-training Classification with mnist Dataset"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Imports and project variables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1.14.0\n"
     ]
    }
   ],
   "source": [
    "import os\n",
    "import shutil\n",
    "from google.cloud import bigquery\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "import tensorflow as tf\n",
    "print(tf.__version__)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Allow you to easily have Python variables in SQL query.\n",
    "from IPython.core.magic import register_cell_magic\n",
    "from IPython import get_ipython\n",
    "\n",
    "\n",
    "@register_cell_magic(\"with_globals\")\n",
    "def with_globals(line, cell):\n",
    "    contents = cell.format(**globals())\n",
    "    if \"print\" in line:\n",
    "        print(contents)\n",
    "    get_ipython().run_cell(contents)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "# change these to try this notebook out\n",
    "# PROJECT = \"cloud-training-demos\"\n",
    "# BUCKET = \"cloud-training-demos-ml\"\n",
    "PROJECT = \"qwiklabs-gcp-8312a1428d9eb5e2\"\n",
    "BUCKET = \"qwiklabs-gcp-8312a1428d9eb5e2-bucket\"\n",
    "REGION = \"us-central1\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "os.environ[\"PROJECT\"] = PROJECT\n",
    "os.environ[\"BUCKET\"] = BUCKET\n",
    "os.environ[\"REGION\"] = REGION"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Create data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "mnist = tf.keras.datasets.mnist\n",
    "\n",
    "(x_train, y_train), (x_test, y_test) = mnist.load_data()\n",
    "x_train, x_test = x_train / 255.0, x_test / 255.0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "x_train.shape = (60000, 28, 28)\n",
      "y_train.shape = (60000,)\n",
      "x_test.shape = (10000, 28, 28)\n",
      "y_test.shape = (10000,)\n"
     ]
    }
   ],
   "source": [
    "print(\"x_train.shape = {}\".format(x_train.shape))\n",
    "print(\"y_train.shape = {}\".format(y_train.shape))\n",
    "print(\"x_test.shape = {}\".format(x_test.shape))\n",
    "print(\"y_test.shape = {}\".format(y_test.shape))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(60000, 784)"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "x_train_flat = x_train.reshape(\n",
    "  x_train.shape[0], x_train.shape[1] * x_train.shape[2])\n",
    "x_train_flat.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(10000, 784)"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "x_test_flat = x_test.reshape(\n",
    "  x_test.shape[0], x_test.shape[1] * x_test.shape[2])\n",
    "x_test_flat.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(60000, 786)"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train = np.concatenate([x_train_flat, np.expand_dims(y_train, -1),\n",
    "                        np.random.rand(x_train_flat.shape[0], 1)],\n",
    "                       axis = 1)\n",
    "train.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(10000, 785)"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test = np.concatenate([x_test_flat,\n",
    "                       np.expand_dims(y_test, -1)],\n",
    "                      axis = 1)\n",
    "test.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>v_0</th>\n",
       "      <th>v_1</th>\n",
       "      <th>v_2</th>\n",
       "      <th>v_3</th>\n",
       "      <th>v_4</th>\n",
       "      <th>v_5</th>\n",
       "      <th>v_6</th>\n",
       "      <th>v_7</th>\n",
       "      <th>v_8</th>\n",
       "      <th>v_9</th>\n",
       "      <th>...</th>\n",
       "      <th>v_776</th>\n",
       "      <th>v_777</th>\n",
       "      <th>v_778</th>\n",
       "      <th>v_779</th>\n",
       "      <th>v_780</th>\n",
       "      <th>v_781</th>\n",
       "      <th>v_782</th>\n",
       "      <th>v_783</th>\n",
       "      <th>label</th>\n",
       "      <th>rand</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>0.287787</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.284469</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0.916785</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.378841</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>0.363079</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 786 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   v_0  v_1  v_2  v_3  v_4  v_5  v_6  v_7  v_8  v_9  ...  v_776  v_777  v_778  \\\n",
       "0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  ...    0.0    0.0    0.0   \n",
       "1  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  ...    0.0    0.0    0.0   \n",
       "2  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  ...    0.0    0.0    0.0   \n",
       "3  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  ...    0.0    0.0    0.0   \n",
       "4  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  ...    0.0    0.0    0.0   \n",
       "\n",
       "   v_779  v_780  v_781  v_782  v_783  label      rand  \n",
       "0    0.0    0.0    0.0    0.0    0.0    5.0  0.287787  \n",
       "1    0.0    0.0    0.0    0.0    0.0    0.0  0.284469  \n",
       "2    0.0    0.0    0.0    0.0    0.0    4.0  0.916785  \n",
       "3    0.0    0.0    0.0    0.0    0.0    1.0  0.378841  \n",
       "4    0.0    0.0    0.0    0.0    0.0    9.0  0.363079  \n",
       "\n",
       "[5 rows x 786 columns]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train_df = pd.DataFrame(\n",
    "  train,\n",
    "  columns=[\"v_\" + str(i)\n",
    "           for i in range(x_train_flat.shape[1])] + [\"label\", \"rand\"])\n",
    "train_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>v_0</th>\n",
       "      <th>v_1</th>\n",
       "      <th>v_2</th>\n",
       "      <th>v_3</th>\n",
       "      <th>v_4</th>\n",
       "      <th>v_5</th>\n",
       "      <th>v_6</th>\n",
       "      <th>v_7</th>\n",
       "      <th>v_8</th>\n",
       "      <th>v_9</th>\n",
       "      <th>...</th>\n",
       "      <th>v_775</th>\n",
       "      <th>v_776</th>\n",
       "      <th>v_777</th>\n",
       "      <th>v_778</th>\n",
       "      <th>v_779</th>\n",
       "      <th>v_780</th>\n",
       "      <th>v_781</th>\n",
       "      <th>v_782</th>\n",
       "      <th>v_783</th>\n",
       "      <th>label</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>7.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 785 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   v_0  v_1  v_2  v_3  v_4  v_5  v_6  v_7  v_8  v_9  ...  v_775  v_776  v_777  \\\n",
       "0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  ...    0.0    0.0    0.0   \n",
       "1  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  ...    0.0    0.0    0.0   \n",
       "2  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  ...    0.0    0.0    0.0   \n",
       "3  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  ...    0.0    0.0    0.0   \n",
       "4  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  ...    0.0    0.0    0.0   \n",
       "\n",
       "   v_778  v_779  v_780  v_781  v_782  v_783  label  \n",
       "0    0.0    0.0    0.0    0.0    0.0    0.0    7.0  \n",
       "1    0.0    0.0    0.0    0.0    0.0    0.0    2.0  \n",
       "2    0.0    0.0    0.0    0.0    0.0    0.0    1.0  \n",
       "3    0.0    0.0    0.0    0.0    0.0    0.0    0.0  \n",
       "4    0.0    0.0    0.0    0.0    0.0    0.0    4.0  \n",
       "\n",
       "[5 rows x 785 columns]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test_df = pd.DataFrame(\n",
    "  test,\n",
    "  columns=[\"v_\" + str(i)\n",
    "           for i in range(x_test_flat.shape[1])] + [\"label\"])\n",
    "test_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>v_0</th>\n",
       "      <th>v_1</th>\n",
       "      <th>v_2</th>\n",
       "      <th>v_3</th>\n",
       "      <th>v_4</th>\n",
       "      <th>v_5</th>\n",
       "      <th>v_6</th>\n",
       "      <th>v_7</th>\n",
       "      <th>v_8</th>\n",
       "      <th>v_9</th>\n",
       "      <th>...</th>\n",
       "      <th>v_776</th>\n",
       "      <th>v_777</th>\n",
       "      <th>v_778</th>\n",
       "      <th>v_779</th>\n",
       "      <th>v_780</th>\n",
       "      <th>v_781</th>\n",
       "      <th>v_782</th>\n",
       "      <th>v_783</th>\n",
       "      <th>label</th>\n",
       "      <th>rand</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>count</td>\n",
       "      <td>60000.0</td>\n",
       "      <td>60000.0</td>\n",
       "      <td>60000.0</td>\n",
       "      <td>60000.0</td>\n",
       "      <td>60000.0</td>\n",
       "      <td>60000.0</td>\n",
       "      <td>60000.0</td>\n",
       "      <td>60000.0</td>\n",
       "      <td>60000.0</td>\n",
       "      <td>60000.0</td>\n",
       "      <td>...</td>\n",
       "      <td>60000.000000</td>\n",
       "      <td>60000.000000</td>\n",
       "      <td>60000.000000</td>\n",
       "      <td>60000.000000</td>\n",
       "      <td>60000.0</td>\n",
       "      <td>60000.0</td>\n",
       "      <td>60000.0</td>\n",
       "      <td>60000.0</td>\n",
       "      <td>60000.000000</td>\n",
       "      <td>60000.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>mean</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000179</td>\n",
       "      <td>0.000076</td>\n",
       "      <td>0.000059</td>\n",
       "      <td>0.000008</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4.453933</td>\n",
       "      <td>0.499276</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>std</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.011137</td>\n",
       "      <td>0.006615</td>\n",
       "      <td>0.006582</td>\n",
       "      <td>0.001359</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.889270</td>\n",
       "      <td>0.288386</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>min</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000022</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>25%</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.000000</td>\n",
       "      <td>0.249056</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>50%</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4.000000</td>\n",
       "      <td>0.498355</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>75%</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>7.000000</td>\n",
       "      <td>0.749470</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>max</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.992157</td>\n",
       "      <td>0.992157</td>\n",
       "      <td>0.996078</td>\n",
       "      <td>0.243137</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>9.000000</td>\n",
       "      <td>0.999963</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>8 rows × 786 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           v_0      v_1      v_2      v_3      v_4      v_5      v_6      v_7  \\\n",
       "count  60000.0  60000.0  60000.0  60000.0  60000.0  60000.0  60000.0  60000.0   \n",
       "mean       0.0      0.0      0.0      0.0      0.0      0.0      0.0      0.0   \n",
       "std        0.0      0.0      0.0      0.0      0.0      0.0      0.0      0.0   \n",
       "min        0.0      0.0      0.0      0.0      0.0      0.0      0.0      0.0   \n",
       "25%        0.0      0.0      0.0      0.0      0.0      0.0      0.0      0.0   \n",
       "50%        0.0      0.0      0.0      0.0      0.0      0.0      0.0      0.0   \n",
       "75%        0.0      0.0      0.0      0.0      0.0      0.0      0.0      0.0   \n",
       "max        0.0      0.0      0.0      0.0      0.0      0.0      0.0      0.0   \n",
       "\n",
       "           v_8      v_9  ...         v_776         v_777         v_778  \\\n",
       "count  60000.0  60000.0  ...  60000.000000  60000.000000  60000.000000   \n",
       "mean       0.0      0.0  ...      0.000179      0.000076      0.000059   \n",
       "std        0.0      0.0  ...      0.011137      0.006615      0.006582   \n",
       "min        0.0      0.0  ...      0.000000      0.000000      0.000000   \n",
       "25%        0.0      0.0  ...      0.000000      0.000000      0.000000   \n",
       "50%        0.0      0.0  ...      0.000000      0.000000      0.000000   \n",
       "75%        0.0      0.0  ...      0.000000      0.000000      0.000000   \n",
       "max        0.0      0.0  ...      0.992157      0.992157      0.996078   \n",
       "\n",
       "              v_779    v_780    v_781    v_782    v_783         label  \\\n",
       "count  60000.000000  60000.0  60000.0  60000.0  60000.0  60000.000000   \n",
       "mean       0.000008      0.0      0.0      0.0      0.0      4.453933   \n",
       "std        0.001359      0.0      0.0      0.0      0.0      2.889270   \n",
       "min        0.000000      0.0      0.0      0.0      0.0      0.000000   \n",
       "25%        0.000000      0.0      0.0      0.0      0.0      2.000000   \n",
       "50%        0.000000      0.0      0.0      0.0      0.0      4.000000   \n",
       "75%        0.000000      0.0      0.0      0.0      0.0      7.000000   \n",
       "max        0.243137      0.0      0.0      0.0      0.0      9.000000   \n",
       "\n",
       "               rand  \n",
       "count  60000.000000  \n",
       "mean       0.499276  \n",
       "std        0.288386  \n",
       "min        0.000022  \n",
       "25%        0.249056  \n",
       "50%        0.498355  \n",
       "75%        0.749470  \n",
       "max        0.999963  \n",
       "\n",
       "[8 rows x 786 columns]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train_df.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>v_0</th>\n",
       "      <th>v_1</th>\n",
       "      <th>v_2</th>\n",
       "      <th>v_3</th>\n",
       "      <th>v_4</th>\n",
       "      <th>v_5</th>\n",
       "      <th>v_6</th>\n",
       "      <th>v_7</th>\n",
       "      <th>v_8</th>\n",
       "      <th>v_9</th>\n",
       "      <th>...</th>\n",
       "      <th>v_775</th>\n",
       "      <th>v_776</th>\n",
       "      <th>v_777</th>\n",
       "      <th>v_778</th>\n",
       "      <th>v_779</th>\n",
       "      <th>v_780</th>\n",
       "      <th>v_781</th>\n",
       "      <th>v_782</th>\n",
       "      <th>v_783</th>\n",
       "      <th>label</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>count</td>\n",
       "      <td>10000.0</td>\n",
       "      <td>10000.0</td>\n",
       "      <td>10000.0</td>\n",
       "      <td>10000.0</td>\n",
       "      <td>10000.0</td>\n",
       "      <td>10000.0</td>\n",
       "      <td>10000.0</td>\n",
       "      <td>10000.0</td>\n",
       "      <td>10000.0</td>\n",
       "      <td>10000.0</td>\n",
       "      <td>...</td>\n",
       "      <td>10000.000000</td>\n",
       "      <td>10000.000000</td>\n",
       "      <td>10000.000000</td>\n",
       "      <td>10000.0</td>\n",
       "      <td>10000.0</td>\n",
       "      <td>10000.0</td>\n",
       "      <td>10000.0</td>\n",
       "      <td>10000.0</td>\n",
       "      <td>10000.0</td>\n",
       "      <td>10000.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>mean</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000642</td>\n",
       "      <td>0.000206</td>\n",
       "      <td>0.000002</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4.443400</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>std</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.022494</td>\n",
       "      <td>0.009490</td>\n",
       "      <td>0.000235</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.895865</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>min</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>25%</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>50%</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>75%</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>7.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>max</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.992157</td>\n",
       "      <td>0.611765</td>\n",
       "      <td>0.023529</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>9.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>8 rows × 785 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           v_0      v_1      v_2      v_3      v_4      v_5      v_6      v_7  \\\n",
       "count  10000.0  10000.0  10000.0  10000.0  10000.0  10000.0  10000.0  10000.0   \n",
       "mean       0.0      0.0      0.0      0.0      0.0      0.0      0.0      0.0   \n",
       "std        0.0      0.0      0.0      0.0      0.0      0.0      0.0      0.0   \n",
       "min        0.0      0.0      0.0      0.0      0.0      0.0      0.0      0.0   \n",
       "25%        0.0      0.0      0.0      0.0      0.0      0.0      0.0      0.0   \n",
       "50%        0.0      0.0      0.0      0.0      0.0      0.0      0.0      0.0   \n",
       "75%        0.0      0.0      0.0      0.0      0.0      0.0      0.0      0.0   \n",
       "max        0.0      0.0      0.0      0.0      0.0      0.0      0.0      0.0   \n",
       "\n",
       "           v_8      v_9  ...         v_775         v_776         v_777  \\\n",
       "count  10000.0  10000.0  ...  10000.000000  10000.000000  10000.000000   \n",
       "mean       0.0      0.0  ...      0.000642      0.000206      0.000002   \n",
       "std        0.0      0.0  ...      0.022494      0.009490      0.000235   \n",
       "min        0.0      0.0  ...      0.000000      0.000000      0.000000   \n",
       "25%        0.0      0.0  ...      0.000000      0.000000      0.000000   \n",
       "50%        0.0      0.0  ...      0.000000      0.000000      0.000000   \n",
       "75%        0.0      0.0  ...      0.000000      0.000000      0.000000   \n",
       "max        0.0      0.0  ...      0.992157      0.611765      0.023529   \n",
       "\n",
       "         v_778    v_779    v_780    v_781    v_782    v_783         label  \n",
       "count  10000.0  10000.0  10000.0  10000.0  10000.0  10000.0  10000.000000  \n",
       "mean       0.0      0.0      0.0      0.0      0.0      0.0      4.443400  \n",
       "std        0.0      0.0      0.0      0.0      0.0      0.0      2.895865  \n",
       "min        0.0      0.0      0.0      0.0      0.0      0.0      0.000000  \n",
       "25%        0.0      0.0      0.0      0.0      0.0      0.0      2.000000  \n",
       "50%        0.0      0.0      0.0      0.0      0.0      0.0      4.000000  \n",
       "75%        0.0      0.0      0.0      0.0      0.0      0.0      7.000000  \n",
       "max        0.0      0.0      0.0      0.0      0.0      0.0      9.000000  \n",
       "\n",
       "[8 rows x 785 columns]"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test_df.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "train_df.to_csv(\"mnist_train.csv\", index=False)\n",
    "test_df.to_csv(\"mnist_test.csv\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "v_0,v_1,v_2,v_3,v_4,v_5,v_6,v_7,v_8,v_9,v_10,v_11,v_12,v_13,v_14,v_15,v_16,v_17,v_18,v_19,v_20,v_21,v_22,v_23,v_24,v_25,v_26,v_27,v_28,v_29,v_30,v_31,v_32,v_33,v_34,v_35,v_36,v_37,v_38,v_39,v_40,v_41,v_42,v_43,v_44,v_45,v_46,v_47,v_48,v_49,v_50,v_51,v_52,v_53,v_54,v_55,v_56,v_57,v_58,v_59,v_60,v_61,v_62,v_63,v_64,v_65,v_66,v_67,v_68,v_69,v_70,v_71,v_72,v_73,v_74,v_75,v_76,v_77,v_78,v_79,v_80,v_81,v_82,v_83,v_84,v_85,v_86,v_87,v_88,v_89,v_90,v_91,v_92,v_93,v_94,v_95,v_96,v_97,v_98,v_99,v_100,v_101,v_102,v_103,v_104,v_105,v_106,v_107,v_108,v_109,v_110,v_111,v_112,v_113,v_114,v_115,v_116,v_117,v_118,v_119,v_120,v_121,v_122,v_123,v_124,v_125,v_126,v_127,v_128,v_129,v_130,v_131,v_132,v_133,v_134,v_135,v_136,v_137,v_138,v_139,v_140,v_141,v_142,v_143,v_144,v_145,v_146,v_147,v_148,v_149,v_150,v_151,v_152,v_153,v_154,v_155,v_156,v_157,v_158,v_159,v_160,v_161,v_162,v_163,v_164,v_165,v_166,v_167,v_168,v_169,v_170,v_171,v_172,v_173,v_174,v_175,v_176,v_177,v_178,v_179,v_180,v_181,v_182,v_183,v_184,v_185,v_186,v_187,v_188,v_189,v_190,v_191,v_192,v_193,v_194,v_195,v_196,v_197,v_198,v_199,v_200,v_201,v_202,v_203,v_204,v_205,v_206,v_207,v_208,v_209,v_210,v_211,v_212,v_213,v_214,v_215,v_216,v_217,v_218,v_219,v_220,v_221,v_222,v_223,v_224,v_225,v_226,v_227,v_228,v_229,v_230,v_231,v_232,v_233,v_234,v_235,v_236,v_237,v_238,v_239,v_240,v_241,v_242,v_243,v_244,v_245,v_246,v_247,v_248,v_249,v_250,v_251,v_252,v_253,v_254,v_255,v_256,v_257,v_258,v_259,v_260,v_261,v_262,v_263,v_264,v_265,v_266,v_267,v_268,v_269,v_270,v_271,v_272,v_273,v_274,v_275,v_276,v_277,v_278,v_279,v_280,v_281,v_282,v_283,v_284,v_285,v_286,v_287,v_288,v_289,v_290,v_291,v_292,v_293,v_294,v_295,v_296,v_297,v_298,v_299,v_300,v_301,v_302,v_303,v_304,v_305,v_306,v_307,v_308,v_309,v_310,v_311,v_312,v_313,v_314,v_315,v_316,v_317,v_318,v_319,v_320,v_321,v_322,v_323,v_324,v_325,v_326,v_327,v_328,v_329,v_330,v_331,v_332,v_333,v_334,v_335,v_336,v_337,v_338,v_339,v_340,v_341,v_342,v_343,v_344,v_345,v_346,v_347,v_348,v_349,v_350,v_351,v_352,v_353,v_354,v_355,v_356,v_357,v_358,v_359,v_360,v_361,v_362,v_363,v_364,v_365,v_366,v_367,v_368,v_369,v_370,v_371,v_372,v_373,v_374,v_375,v_376,v_377,v_378,v_379,v_380,v_381,v_382,v_383,v_384,v_385,v_386,v_387,v_388,v_389,v_390,v_391,v_392,v_393,v_394,v_395,v_396,v_397,v_398,v_399,v_400,v_401,v_402,v_403,v_404,v_405,v_406,v_407,v_408,v_409,v_410,v_411,v_412,v_413,v_414,v_415,v_416,v_417,v_418,v_419,v_420,v_421,v_422,v_423,v_424,v_425,v_426,v_427,v_428,v_429,v_430,v_431,v_432,v_433,v_434,v_435,v_436,v_437,v_438,v_439,v_440,v_441,v_442,v_443,v_444,v_445,v_446,v_447,v_448,v_449,v_450,v_451,v_452,v_453,v_454,v_455,v_456,v_457,v_458,v_459,v_460,v_461,v_462,v_463,v_464,v_465,v_466,v_467,v_468,v_469,v_470,v_471,v_472,v_473,v_474,v_475,v_476,v_477,v_478,v_479,v_480,v_481,v_482,v_483,v_484,v_485,v_486,v_487,v_488,v_489,v_490,v_491,v_492,v_493,v_494,v_495,v_496,v_497,v_498,v_499,v_500,v_501,v_502,v_503,v_504,v_505,v_506,v_507,v_508,v_509,v_510,v_511,v_512,v_513,v_514,v_515,v_516,v_517,v_518,v_519,v_520,v_521,v_522,v_523,v_524,v_525,v_526,v_527,v_528,v_529,v_530,v_531,v_532,v_533,v_534,v_535,v_536,v_537,v_538,v_539,v_540,v_541,v_542,v_543,v_544,v_545,v_546,v_547,v_548,v_549,v_550,v_551,v_552,v_553,v_554,v_555,v_556,v_557,v_558,v_559,v_560,v_561,v_562,v_563,v_564,v_565,v_566,v_567,v_568,v_569,v_570,v_571,v_572,v_573,v_574,v_575,v_576,v_577,v_578,v_579,v_580,v_581,v_582,v_583,v_584,v_585,v_586,v_587,v_588,v_589,v_590,v_591,v_592,v_593,v_594,v_595,v_596,v_597,v_598,v_599,v_600,v_601,v_602,v_603,v_604,v_605,v_606,v_607,v_608,v_609,v_610,v_611,v_612,v_613,v_614,v_615,v_616,v_617,v_618,v_619,v_620,v_621,v_622,v_623,v_624,v_625,v_626,v_627,v_628,v_629,v_630,v_631,v_632,v_633,v_634,v_635,v_636,v_637,v_638,v_639,v_640,v_641,v_642,v_643,v_644,v_645,v_646,v_647,v_648,v_649,v_650,v_651,v_652,v_653,v_654,v_655,v_656,v_657,v_658,v_659,v_660,v_661,v_662,v_663,v_664,v_665,v_666,v_667,v_668,v_669,v_670,v_671,v_672,v_673,v_674,v_675,v_676,v_677,v_678,v_679,v_680,v_681,v_682,v_683,v_684,v_685,v_686,v_687,v_688,v_689,v_690,v_691,v_692,v_693,v_694,v_695,v_696,v_697,v_698,v_699,v_700,v_701,v_702,v_703,v_704,v_705,v_706,v_707,v_708,v_709,v_710,v_711,v_712,v_713,v_714,v_715,v_716,v_717,v_718,v_719,v_720,v_721,v_722,v_723,v_724,v_725,v_726,v_727,v_728,v_729,v_730,v_731,v_732,v_733,v_734,v_735,v_736,v_737,v_738,v_739,v_740,v_741,v_742,v_743,v_744,v_745,v_746,v_747,v_748,v_749,v_750,v_751,v_752,v_753,v_754,v_755,v_756,v_757,v_758,v_759,v_760,v_761,v_762,v_763,v_764,v_765,v_766,v_767,v_768,v_769,v_770,v_771,v_772,v_773,v_774,v_775,v_776,v_777,v_778,v_779,v_780,v_781,v_782,v_783,label,rand\n",
      "0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.011764705882352941,0.07058823529411765,0.07058823529411765,0.07058823529411765,0.49411764705882355,0.5333333333333333,0.6862745098039216,0.10196078431372549,0.6509803921568628,1.0,0.9686274509803922,0.4980392156862745,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.11764705882352941,0.1411764705882353,0.3686274509803922,0.6039215686274509,0.6666666666666666,0.9921568627450981,0.9921568627450981,0.9921568627450981,0.9921568627450981,0.9921568627450981,0.8823529411764706,0.6745098039215687,0.9921568627450981,0.9490196078431372,0.7647058823529411,0.25098039215686274,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.19215686274509805,0.9333333333333333,0.9921568627450981,0.9921568627450981,0.9921568627450981,0.9921568627450981,0.9921568627450981,0.9921568627450981,0.9921568627450981,0.9921568627450981,0.984313725490196,0.36470588235294116,0.3215686274509804,0.3215686274509804,0.2196078431372549,0.15294117647058825,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.07058823529411765,0.8588235294117647,0.9921568627450981,0.9921568627450981,0.9921568627450981,0.9921568627450981,0.9921568627450981,0.7764705882352941,0.7137254901960784,0.9686274509803922,0.9450980392156862,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.3137254901960784,0.611764705882353,0.4196078431372549,0.9921568627450981,0.9921568627450981,0.803921568627451,0.043137254901960784,0.0,0.16862745098039217,0.6039215686274509,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.054901960784313725,0.00392156862745098,0.6039215686274509,0.9921568627450981,0.35294117647058826,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.5450980392156862,0.9921568627450981,0.7450980392156863,0.00784313725490196,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.043137254901960784,0.7450980392156863,0.9921568627450981,0.27450980392156865,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.13725490196078433,0.9450980392156862,0.8823529411764706,0.6274509803921569,0.4235294117647059,0.00392156862745098,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.3176470588235294,0.9411764705882353,0.9921568627450981,0.9921568627450981,0.4666666666666667,0.09803921568627451,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.17647058823529413,0.7294117647058823,0.9921568627450981,0.9921568627450981,0.5882352941176471,0.10588235294117647,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.06274509803921569,0.36470588235294116,0.9882352941176471,0.9921568627450981,0.7333333333333333,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.9764705882352941,0.9921568627450981,0.9764705882352941,0.25098039215686274,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.1803921568627451,0.5098039215686274,0.7176470588235294,0.9921568627450981,0.9921568627450981,0.8117647058823529,0.00784313725490196,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.15294117647058825,0.5803921568627451,0.8980392156862745,0.9921568627450981,0.9921568627450981,0.9921568627450981,0.9803921568627451,0.7137254901960784,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.09411764705882353,0.4470588235294118,0.8666666666666667,0.9921568627450981,0.9921568627450981,0.9921568627450981,0.9921568627450981,0.788235294117647,0.3058823529411765,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.09019607843137255,0.25882352941176473,0.8352941176470589,0.9921568627450981,0.9921568627450981,0.9921568627450981,0.9921568627450981,0.7764705882352941,0.3176470588235294,0.00784313725490196,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.07058823529411765,0.6705882352941176,0.8588235294117647,0.9921568627450981,0.9921568627450981,0.9921568627450981,0.9921568627450981,0.7647058823529411,0.3137254901960784,0.03529411764705882,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.21568627450980393,0.6745098039215687,0.8862745098039215,0.9921568627450981,0.9921568627450981,0.9921568627450981,0.9921568627450981,0.9568627450980393,0.5215686274509804,0.043137254901960784,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.5333333333333333,0.9921568627450981,0.9921568627450981,0.9921568627450981,0.8313725490196079,0.5294117647058824,0.5176470588235295,0.06274509803921569,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,5.0,0.28778708060614955\n"
     ]
    }
   ],
   "source": [
    "!head -2 mnist_train.csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%bash\n",
    "gcloud storage cp mnist*.csv gs://${BUCKET}"   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Write data to BigQuery"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "client = bigquery.Client()\n",
    "dataset_id = \"semi\"\n",
    "dataset_ref = client.dataset(dataset_id)\n",
    "feature_schema = [bigquery.SchemaField(\n",
    "  name=\"v_{}\".format(i),\n",
    "  field_type=\"FLOAT64\",\n",
    "  mode=\"NULLABLE\",\n",
    "  description=\"Feature {}\".format(i))\n",
    "                  for i in range(x_train_flat.shape[-1])]\n",
    "label_schema = [bigquery.SchemaField(\n",
    "  name=\"label\",\n",
    "  field_type=\"FLOAT64\",\n",
    "  mode=\"NULLABLE\",\n",
    "  description=\"Label\")]\n",
    "rand_schema = [bigquery.SchemaField(\n",
    "  name=\"rand\",\n",
    "  field_type=\"FLOAT64\",\n",
    "  mode=\"NULLABLE\",\n",
    "  description=\"Random number\")]\n",
    "job_config = bigquery.LoadJobConfig()\n",
    "job_config.schema = feature_schema + label_schema + rand_schema\n",
    "job_config.write_disposition = bigquery.WriteDisposition.WRITE_TRUNCATE\n",
    "job_config.skip_leading_rows = 1\n",
    "# The source format defaults to CSV, so the line below is optional.\n",
    "job_config.source_format = bigquery.SourceFormat.CSV"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "def load_csv_data_to_bigquery(client, dataset_ref, job_config, name):\n",
    "  uri = \"gs://{bucket}/{name}.csv\".format(bucket=BUCKET, name=name)\n",
    "\n",
    "  load_job = client.load_table_from_uri(\n",
    "      uri, dataset_ref.table(name), job_config=job_config\n",
    "  )  # API request\n",
    "  print(\"Starting job {}\".format(load_job.job_id))\n",
    "\n",
    "  load_job.result()  # Waits for table load to complete.\n",
    "  print(\"Job finished.\")\n",
    "\n",
    "  destination_table = client.get_table(dataset_ref.table(name))\n",
    "  print(\"Loaded {} rows.\".format(destination_table.num_rows))\n",
    "\n",
    "  return None"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Train set"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Starting job d0624e4a-8806-4f6b-8758-2305f7bd447c\n",
      "Job finished.\n",
      "Loaded 60000 rows.\n"
     ]
    }
   ],
   "source": [
    "job_config.schema = feature_schema + label_schema + rand_schema\n",
    "load_csv_data_to_bigquery(client, dataset_ref, job_config, \"mnist_train\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Test set"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Starting job 3e514686-96b1-4919-bff9-4c67a1c6d323\n",
      "Job finished.\n",
      "Loaded 10000 rows.\n"
     ]
    }
   ],
   "source": [
    "job_config.schema = feature_schema + label_schema\n",
    "load_csv_data_to_bigquery(client, dataset_ref, job_config, \"mnist_test\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Create semi-supervised simulated splits"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "PERCENT_LABELED = 10.0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "def create_semi_supervised_simulated_splits_in_bigquery(dataset_id, sql, name):\n",
    "  job_config = bigquery.QueryJobConfig()\n",
    "  # Set the destination table\n",
    "  table_ref = client.dataset(dataset_id).table(name)\n",
    "  job_config.destination = table_ref\n",
    "  job_config.write_disposition = bigquery.WriteDisposition.WRITE_TRUNCATE\n",
    "  # Start the query, passing in the extra configuration.\n",
    "  query_job = client.query(\n",
    "      sql,\n",
    "      # Location must match that of the dataset(s) referenced in the query\n",
    "      # and of the destination table.\n",
    "      location=\"US\",\n",
    "      job_config=job_config)  # API request - starts the query\n",
    "\n",
    "  query_job.result()  # Waits for the query to finish\n",
    "  print('Query results loaded to table {}'.format(table_ref.path))\n",
    "\n",
    "  return None"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Labeled"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "def create_labeled_train_set(project, dataset_id, percent_labeled):\n",
    "  mnist_train_labeled_sql = \"\"\"\n",
    "  SELECT\n",
    "    * EXCEPT(rand)\n",
    "  FROM\n",
    "    `{project}.{dataset}.{table}`\n",
    "  WHERE rand < {percent}\n",
    "  \"\"\".format(\n",
    "    project=project,\n",
    "    dataset=dataset_id,\n",
    "    table=\"mnist_train\",\n",
    "    percent=percent_labeled / 100.0)\n",
    "\n",
    "  create_semi_supervised_simulated_splits_in_bigquery(\n",
    "    dataset_id, mnist_train_labeled_sql, \"mnist_train_labeled\")\n",
    "\n",
    "  return None"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Query results loaded to table /projects/qwiklabs-gcp-8312a1428d9eb5e2/datasets/semi/tables/mnist_train_labeled\n"
     ]
    }
   ],
   "source": [
    "create_labeled_train_set(PROJECT, dataset_id, PERCENT_LABELED)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Unlabeled"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "def create_unlabeled_train_set(project, dataset_id, percent_labeled):\n",
    "  mnist_train_unlabeled_sql = \"\"\"\n",
    "  SELECT\n",
    "    * EXCEPT(rand)\n",
    "  FROM\n",
    "    `{project}.{dataset}.{table}`\n",
    "  WHERE rand >= {percent}\n",
    "  \"\"\".format(\n",
    "    project=project,\n",
    "    dataset=dataset_id,\n",
    "    table=\"mnist_train\",\n",
    "    percent=percent_labeled / 100.0)\n",
    "\n",
    "  create_semi_supervised_simulated_splits_in_bigquery(\n",
    "    dataset_id, mnist_train_unlabeled_sql, \"mnist_train_unlabeled\")\n",
    "\n",
    "  return None"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Query results loaded to table /projects/qwiklabs-gcp-8312a1428d9eb5e2/datasets/semi/tables/mnist_train_unlabeled\n"
     ]
    }
   ],
   "source": [
    "create_unlabeled_train_set(PROJECT, dataset_id, PERCENT_LABELED)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## BQML"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Train model on labeled train set"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "def bqml_train_model_on_labeled_dataset():\n",
    "  query_job = client.query(\"\"\"\n",
    "  CREATE OR REPLACE MODEL\n",
    "    `bqml_ssl.self_training`\n",
    "  OPTIONS\n",
    "    ( model_type=\"logistic_reg\",\n",
    "      auto_class_weights=true,\n",
    "      input_label_cols = [\"label\"]) AS\n",
    "  SELECT\n",
    "    *\n",
    "  FROM\n",
    "    `semi.mnist_train_labeled`\n",
    "  \"\"\")\n",
    "\n",
    "  try:\n",
    "    query_job.result()\n",
    "  finally:\n",
    "    print(\"Training complete.\")\n",
    "\n",
    "  return None"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Training complete.\n"
     ]
    }
   ],
   "source": [
    "bqml_train_model_on_labeled_dataset()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Look at training info"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "def bqml_training_info():\n",
    "  query_job = client.query(\"\"\"\n",
    "  SELECT\n",
    "      *\n",
    "  FROM\n",
    "      ML.TRAINING_INFO(MODEL `bqml_ssl.self_training`)\n",
    "  \"\"\")\n",
    "\n",
    "  results = query_job.result()  # Waits for job to complete.\n",
    "\n",
    "  return results"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>duration_ms</th>\n",
       "      <th>eval_loss</th>\n",
       "      <th>iteration</th>\n",
       "      <th>learning_rate</th>\n",
       "      <th>loss</th>\n",
       "      <th>training_run</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>56049</td>\n",
       "      <td>0.034381</td>\n",
       "      <td>9</td>\n",
       "      <td>1.6</td>\n",
       "      <td>0.025464</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>48802</td>\n",
       "      <td>0.034698</td>\n",
       "      <td>8</td>\n",
       "      <td>0.8</td>\n",
       "      <td>0.026480</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>45422</td>\n",
       "      <td>0.035927</td>\n",
       "      <td>7</td>\n",
       "      <td>3.2</td>\n",
       "      <td>0.028079</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>50042</td>\n",
       "      <td>0.037224</td>\n",
       "      <td>6</td>\n",
       "      <td>1.6</td>\n",
       "      <td>0.030945</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>56957</td>\n",
       "      <td>0.039116</td>\n",
       "      <td>5</td>\n",
       "      <td>0.8</td>\n",
       "      <td>0.033941</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>48610</td>\n",
       "      <td>0.040741</td>\n",
       "      <td>4</td>\n",
       "      <td>0.4</td>\n",
       "      <td>0.036025</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>6</td>\n",
       "      <td>50531</td>\n",
       "      <td>0.047081</td>\n",
       "      <td>3</td>\n",
       "      <td>1.6</td>\n",
       "      <td>0.041920</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>7</td>\n",
       "      <td>55793</td>\n",
       "      <td>0.051897</td>\n",
       "      <td>2</td>\n",
       "      <td>0.8</td>\n",
       "      <td>0.049430</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>8</td>\n",
       "      <td>55009</td>\n",
       "      <td>0.069936</td>\n",
       "      <td>1</td>\n",
       "      <td>0.4</td>\n",
       "      <td>0.068228</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>9</td>\n",
       "      <td>49424</td>\n",
       "      <td>0.115370</td>\n",
       "      <td>0</td>\n",
       "      <td>0.2</td>\n",
       "      <td>0.114392</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   duration_ms  eval_loss  iteration  learning_rate      loss  training_run\n",
       "0        56049   0.034381          9            1.6  0.025464             0\n",
       "1        48802   0.034698          8            0.8  0.026480             0\n",
       "2        45422   0.035927          7            3.2  0.028079             0\n",
       "3        50042   0.037224          6            1.6  0.030945             0\n",
       "4        56957   0.039116          5            0.8  0.033941             0\n",
       "5        48610   0.040741          4            0.4  0.036025             0\n",
       "6        50531   0.047081          3            1.6  0.041920             0\n",
       "7        55793   0.051897          2            0.8  0.049430             0\n",
       "8        55009   0.069936          1            0.4  0.068228             0\n",
       "9        49424   0.115370          0            0.2  0.114392             0"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.DataFrame([{key: value for key, value in row.items()} for row in bqml_training_info()])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Evaluate on test set"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "def bqml_evaluate_on_test_dataset():\n",
    "  query_job = client.query(\"\"\"\n",
    "  SELECT\n",
    "    *\n",
    "  FROM\n",
    "    ML.EVALUATE(MODEL `bqml_ssl.self_training`,\n",
    "    (SELECT * FROM `semi.mnist_test`))\n",
    "  \"\"\")\n",
    "\n",
    "  results = query_job.result()  # Waits for job to complete.\n",
    "\n",
    "  return results"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>accuracy</th>\n",
       "      <th>f1_score</th>\n",
       "      <th>log_loss</th>\n",
       "      <th>precision</th>\n",
       "      <th>recall</th>\n",
       "      <th>roc_auc</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>0.8993</td>\n",
       "      <td>0.897872</td>\n",
       "      <td>1.840486</td>\n",
       "      <td>0.898469</td>\n",
       "      <td>0.898071</td>\n",
       "      <td>0.960578</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   accuracy  f1_score  log_loss  precision    recall   roc_auc\n",
       "0    0.8993  0.897872  1.840486   0.898469  0.898071  0.960578"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.DataFrame([{key: value for key, value in row.items()}\n",
    "              for row in bqml_evaluate_on_test_dataset()])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Predict on unlabeled train set"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "def bqml_predict_unlabeled_dataset():\n",
    "  query_job = client.query(\"\"\"\n",
    "  SELECT\n",
    "      * EXCEPT(predicted_label_probs, label)\n",
    "  FROM\n",
    "      ML.PREDICT(MODEL `bqml_ssl.self_training`,\n",
    "                 (SELECT * FROM `semi.mnist_train_unlabeled` LIMIT 10)),\n",
    "    UNNEST(predicted_label_probs) AS unnested_predicted_label_probs\n",
    "  \"\"\")\n",
    "\n",
    "  results = query_job.result()  # Waits for job to complete.\n",
    "\n",
    "  return results"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>predicted_label</th>\n",
       "      <th>prob</th>\n",
       "      <th>v_0</th>\n",
       "      <th>v_1</th>\n",
       "      <th>v_10</th>\n",
       "      <th>v_100</th>\n",
       "      <th>v_101</th>\n",
       "      <th>v_102</th>\n",
       "      <th>v_103</th>\n",
       "      <th>v_104</th>\n",
       "      <th>...</th>\n",
       "      <th>v_90</th>\n",
       "      <th>v_91</th>\n",
       "      <th>v_92</th>\n",
       "      <th>v_93</th>\n",
       "      <th>v_94</th>\n",
       "      <th>v_95</th>\n",
       "      <th>v_96</th>\n",
       "      <th>v_97</th>\n",
       "      <th>v_98</th>\n",
       "      <th>v_99</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.149127</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.133352</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.133298</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.127621</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.107521</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>95</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.061369</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>96</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.059155</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>97</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.058982</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>98</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.058966</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>99</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.058668</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>100 rows × 786 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    predicted_label      prob  v_0  v_1  v_10  v_100  v_101  v_102  v_103  \\\n",
       "0               0.0  0.149127  0.0  0.0   0.0    0.0    0.0    0.0    0.0   \n",
       "1               0.0  0.133352  0.0  0.0   0.0    0.0    0.0    0.0    0.0   \n",
       "2               0.0  0.133298  0.0  0.0   0.0    0.0    0.0    0.0    0.0   \n",
       "3               0.0  0.127621  0.0  0.0   0.0    0.0    0.0    0.0    0.0   \n",
       "4               0.0  0.107521  0.0  0.0   0.0    0.0    0.0    0.0    0.0   \n",
       "..              ...       ...  ...  ...   ...    ...    ...    ...    ...   \n",
       "95              0.0  0.061369  0.0  0.0   0.0    0.0    0.0    0.0    0.0   \n",
       "96              0.0  0.059155  0.0  0.0   0.0    0.0    0.0    0.0    0.0   \n",
       "97              0.0  0.058982  0.0  0.0   0.0    0.0    0.0    0.0    0.0   \n",
       "98              0.0  0.058966  0.0  0.0   0.0    0.0    0.0    0.0    0.0   \n",
       "99              0.0  0.058668  0.0  0.0   0.0    0.0    0.0    0.0    0.0   \n",
       "\n",
       "    v_104  ...  v_90  v_91  v_92  v_93  v_94  v_95  v_96  v_97  v_98  v_99  \n",
       "0     0.0  ...   0.0   0.0   0.0   0.0   0.0   0.0   0.0   0.0   0.0   0.0  \n",
       "1     0.0  ...   0.0   0.0   0.0   0.0   0.0   0.0   0.0   0.0   0.0   0.0  \n",
       "2     0.0  ...   0.0   0.0   0.0   0.0   0.0   0.0   0.0   0.0   0.0   0.0  \n",
       "3     0.0  ...   0.0   0.0   0.0   0.0   0.0   0.0   0.0   0.0   0.0   0.0  \n",
       "4     0.0  ...   0.0   0.0   0.0   0.0   0.0   0.0   0.0   0.0   0.0   0.0  \n",
       "..    ...  ...   ...   ...   ...   ...   ...   ...   ...   ...   ...   ...  \n",
       "95    0.0  ...   0.0   0.0   0.0   0.0   0.0   0.0   0.0   0.0   0.0   0.0  \n",
       "96    0.0  ...   0.0   0.0   0.0   0.0   0.0   0.0   0.0   0.0   0.0   0.0  \n",
       "97    0.0  ...   0.0   0.0   0.0   0.0   0.0   0.0   0.0   0.0   0.0   0.0  \n",
       "98    0.0  ...   0.0   0.0   0.0   0.0   0.0   0.0   0.0   0.0   0.0   0.0  \n",
       "99    0.0  ...   0.0   0.0   0.0   0.0   0.0   0.0   0.0   0.0   0.0   0.0  \n",
       "\n",
       "[100 rows x 786 columns]"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.DataFrame([{key: value for key, value in row.items()}\n",
    "              for row in bqml_predict_unlabeled_dataset()])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Check confidence"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [],
   "source": [
    "percent_over_random = 80.0\n",
    "number_of_classes = 10\n",
    "confidence_percent = (1.0 + percent_over_random / 100.0) / number_of_classes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [],
   "source": [
    "features_list = [\"v_{}\".format(i) for i in range(x_train_flat.shape[-1])]\n",
    "features = \",\\n  \".join(features_list)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [],
   "source": [
    "confidence_query = \"\"\"\n",
    "WITH\n",
    "  CTE_gen_ids AS (\n",
    "  SELECT\n",
    "    ROW_NUMBER() OVER () AS row_id,\n",
    "    *\n",
    "  FROM\n",
    "    ML.PREDICT(MODEL `bqml_ssl.self_training`,\n",
    "      (\n",
    "      SELECT\n",
    "        *\n",
    "      FROM\n",
    "        `semi.mnist_train_unlabeled`))),\n",
    "  CTE_max_probs AS (\n",
    "  SELECT\n",
    "    row_id,\n",
    "    MAX(unnested_predicted_label_probs.prob) AS max_prob\n",
    "  FROM\n",
    "    CTE_gen_ids,\n",
    "    UNNEST(predicted_label_probs) AS unnested_predicted_label_probs\n",
    "  GROUP BY\n",
    "    row_id),\n",
    "  CTE_filtered_max_probs AS (\n",
    "  SELECT\n",
    "    *\n",
    "  FROM\n",
    "    CTE_max_probs\n",
    "  WHERE\n",
    "    max_prob {inequality} {confidence_percent})\n",
    "SELECT\n",
    "  {features}{label}\n",
    "FROM\n",
    "  CTE_filtered_max_probs AS A\n",
    "INNER JOIN\n",
    "  CTE_gen_ids AS B\n",
    "ON\n",
    "  A.row_id = B.row_id\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [],
   "source": [
    "high_confidence_features_label_query = confidence_query.format(\n",
    "  inequality=\">=\",\n",
    "  confidence_percent=confidence_percent,\n",
    "  features=features,\n",
    "  label=\", predicted_label AS label\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [],
   "source": [
    "high_confidence_features_query = confidence_query.format(\n",
    "  inequality=\">=\",\n",
    "  confidence_percent=confidence_percent,\n",
    "  features=features,\n",
    "  label=\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [],
   "source": [
    "low_confidence_features_query = confidence_query.format(\n",
    "  inequality=\"<\",\n",
    "  confidence_percent=confidence_percent,\n",
    "  features=features,\n",
    "  label=\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>v_0</th>\n",
       "      <th>v_1</th>\n",
       "      <th>v_2</th>\n",
       "      <th>v_3</th>\n",
       "      <th>v_4</th>\n",
       "      <th>v_5</th>\n",
       "      <th>v_6</th>\n",
       "      <th>v_7</th>\n",
       "      <th>v_8</th>\n",
       "      <th>v_9</th>\n",
       "      <th>...</th>\n",
       "      <th>v_775</th>\n",
       "      <th>v_776</th>\n",
       "      <th>v_777</th>\n",
       "      <th>v_778</th>\n",
       "      <th>v_779</th>\n",
       "      <th>v_780</th>\n",
       "      <th>v_781</th>\n",
       "      <th>v_782</th>\n",
       "      <th>v_783</th>\n",
       "      <th>label</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>8.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>7.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>7.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>457</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>458</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>459</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>9.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>460</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>7.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>461</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>8.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>462 rows × 785 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     v_0  v_1  v_2  v_3  v_4  v_5  v_6  v_7  v_8  v_9  ...  v_775  v_776  \\\n",
       "0    0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  ...    0.0    0.0   \n",
       "1    0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  ...    0.0    0.0   \n",
       "2    0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  ...    0.0    0.0   \n",
       "3    0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  ...    0.0    0.0   \n",
       "4    0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  ...    0.0    0.0   \n",
       "..   ...  ...  ...  ...  ...  ...  ...  ...  ...  ...  ...    ...    ...   \n",
       "457  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  ...    0.0    0.0   \n",
       "458  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  ...    0.0    0.0   \n",
       "459  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  ...    0.0    0.0   \n",
       "460  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  ...    0.0    0.0   \n",
       "461  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  0.0  ...    0.0    0.0   \n",
       "\n",
       "     v_777  v_778  v_779  v_780  v_781  v_782  v_783  label  \n",
       "0      0.0    0.0    0.0    0.0    0.0    0.0    0.0    8.0  \n",
       "1      0.0    0.0    0.0    0.0    0.0    0.0    0.0    2.0  \n",
       "2      0.0    0.0    0.0    0.0    0.0    0.0    0.0    4.0  \n",
       "3      0.0    0.0    0.0    0.0    0.0    0.0    0.0    7.0  \n",
       "4      0.0    0.0    0.0    0.0    0.0    0.0    0.0    7.0  \n",
       "..     ...    ...    ...    ...    ...    ...    ...    ...  \n",
       "457    0.0    0.0    0.0    0.0    0.0    0.0    0.0    3.0  \n",
       "458    0.0    0.0    0.0    0.0    0.0    0.0    0.0    2.0  \n",
       "459    0.0    0.0    0.0    0.0    0.0    0.0    0.0    9.0  \n",
       "460    0.0    0.0    0.0    0.0    0.0    0.0    0.0    7.0  \n",
       "461    0.0    0.0    0.0    0.0    0.0    0.0    0.0    8.0  \n",
       "\n",
       "[462 rows x 785 columns]"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%with_globals\n",
    "%%bigquery --project $PROJECT\n",
    "{high_confidence_features_label_query}"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Check initial table counts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>row_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>5963</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   row_count\n",
       "0       5963"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%with_globals\n",
    "%%bigquery --project $PROJECT\n",
    "SELECT COUNT(*) AS row_count FROM `{PROJECT}.semi.mnist_train_labeled`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>row_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>54037</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   row_count\n",
       "0      54037"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%with_globals\n",
    "%%bigquery --project $PROJECT\n",
    "SELECT COUNT(*) AS row_count FROM `{PROJECT}.semi.mnist_train_unlabeled`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>row_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>462</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   row_count\n",
       "0        462"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%with_globals\n",
    "%%bigquery --project $PROJECT\n",
    "SELECT COUNT(*) AS row_count\n",
    "FROM ({high_confidence_features_query})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>row_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>53575</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   row_count\n",
       "0      53575"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%with_globals\n",
    "%%bigquery --project $PROJECT\n",
    "SELECT COUNT(*) AS row_count\n",
    "FROM ({low_confidence_features_query})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Adjust tables based on confidence of predictions"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Add high confidence examples to labeled dataset with predicted labels"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [],
   "source": [
    "def add_high_confidence_examples_to_labeled(\n",
    "  dataset_id, high_confidence_features_label_query):\n",
    "  job_config = bigquery.QueryJobConfig()\n",
    "  # Set the destination table\n",
    "  table_ref = client.dataset(dataset_id).table(\"mnist_train_labeled\")\n",
    "  job_config.destination = table_ref\n",
    "  job_config.write_disposition = bigquery.WriteDisposition.WRITE_APPEND\n",
    "  # Start the query, passing in the extra configuration.\n",
    "  query_job = client.query(\n",
    "      high_confidence_features_label_query,\n",
    "      # Location must match that of the dataset(s) referenced in the query\n",
    "      # and of the destination table.\n",
    "      location=\"US\",\n",
    "      job_config=job_config)  # API request - starts the query\n",
    "\n",
    "  query_job.result()  # Waits for the query to finish\n",
    "  print('Query results loaded to table {}'.format(table_ref.path))\n",
    "\n",
    "  return None"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Query results loaded to table /projects/qwiklabs-gcp-8312a1428d9eb5e2/datasets/semi/tables/mnist_train_labeled\n"
     ]
    }
   ],
   "source": [
    "add_high_confidence_examples_to_labeled(\n",
    "  dataset_id, high_confidence_features_label_query)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Check updated table counts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>row_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>6425</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   row_count\n",
       "0       6425"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%with_globals\n",
    "%%bigquery --project $PROJECT\n",
    "SELECT COUNT(*) AS row_count FROM `{PROJECT}.semi.mnist_train_labeled`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>row_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>54037</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   row_count\n",
       "0      54037"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%with_globals\n",
    "%%bigquery --project $PROJECT\n",
    "SELECT COUNT(*) AS row_count FROM `{PROJECT}.semi.mnist_train_unlabeled`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Remove high confidence examples from unlabeled dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [],
   "source": [
    "def remove_high_confidence_examples_from_unlabeled(\n",
    "  dataset_id, low_confidence_features_query):\n",
    "  job_config = bigquery.QueryJobConfig()\n",
    "  # Set the destination table\n",
    "  table_ref = client.dataset(dataset_id).table(\"mnist_train_unlabeled\")\n",
    "  job_config.destination = table_ref\n",
    "  job_config.write_disposition = bigquery.WriteDisposition.WRITE_TRUNCATE\n",
    "  # Start the query, passing in the extra configuration.\n",
    "  query_job = client.query(\n",
    "      low_confidence_features_query,\n",
    "      # Location must match that of the dataset(s) referenced in the query\n",
    "      # and of the destination table.\n",
    "      location=\"US\",\n",
    "      job_config=job_config)  # API request - starts the query\n",
    "\n",
    "  query_job.result()  # Waits for the query to finish\n",
    "  print('Query results loaded to table {}'.format(table_ref.path))\n",
    "\n",
    "  return None"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Query results loaded to table /projects/qwiklabs-gcp-8312a1428d9eb5e2/datasets/semi/tables/mnist_train_unlabeled\n"
     ]
    }
   ],
   "source": [
    "remove_high_confidence_examples_from_unlabeled(\n",
    "  dataset_id, low_confidence_features_query)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Check updated table counts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>row_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>6425</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   row_count\n",
       "0       6425"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%with_globals\n",
    "%%bigquery --project $PROJECT\n",
    "SELECT COUNT(*) AS row_count FROM `{PROJECT}.semi.mnist_train_labeled`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>row_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>53575</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   row_count\n",
       "0      53575"
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%with_globals\n",
    "%%bigquery --project $PROJECT\n",
    "SELECT COUNT(*) AS row_count FROM `{PROJECT}.semi.mnist_train_unlabeled`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Semi-supervised Self-training Loop"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Reset labeled and unlabeled datasets"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Query results loaded to table /projects/qwiklabs-gcp-8312a1428d9eb5e2/datasets/semi/tables/mnist_train_labeled\n"
     ]
    }
   ],
   "source": [
    "create_labeled_train_set(PROJECT, dataset_id, PERCENT_LABELED)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Query results loaded to table /projects/qwiklabs-gcp-8312a1428d9eb5e2/datasets/semi/tables/mnist_train_unlabeled\n"
     ]
    }
   ],
   "source": [
    "create_unlabeled_train_set(PROJECT, dataset_id, PERCENT_LABELED)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Loop until no improvement"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Iteration = 0\n",
      "Starting training.\n",
      "Training complete.\n",
      "Starting evaluation.\n",
      "eval_metrics =    accuracy  f1_score  log_loss  precision    recall   roc_auc\n",
      "0    0.8993  0.897872  1.840486   0.898469  0.898071  0.960578\n",
      "Adding high confidence examples to labeled.\n",
      "Query results loaded to table /projects/qwiklabs-gcp-8312a1428d9eb5e2/datasets/semi/tables/mnist_train_labeled\n",
      "Removing high confidence examples from unlabeled.\n",
      "Query results loaded to table /projects/qwiklabs-gcp-8312a1428d9eb5e2/datasets/semi/tables/mnist_train_unlabeled\n",
      "Iteration = 1\n",
      "Starting training.\n",
      "Training complete.\n",
      "Starting evaluation.\n",
      "eval_metrics =    accuracy  f1_score  log_loss  precision    recall   roc_auc\n",
      "0    0.9005  0.898888  1.839274   0.899643  0.899122  0.959887\n",
      "Not enough improvement, breaking loop!\n"
     ]
    }
   ],
   "source": [
    "old_accuracy = 0.0\n",
    "max_iterations = 5\n",
    "iteration = 0\n",
    "while iteration < max_iterations:\n",
    "  print(\"Iteration = {}\".format(iteration))\n",
    "\n",
    "  # Train model on labeled dataset\n",
    "  print(\"Starting training.\")\n",
    "  bqml_train_model_on_labeled_dataset()\n",
    "\n",
    "  # Evaluate model on test set\n",
    "  print(\"Starting evaluation.\")\n",
    "  eval_metrics = pd.DataFrame([{key: value for key, value in row.items()}\n",
    "                               for row in bqml_evaluate_on_test_dataset()])\n",
    "  print(\"eval_metrics = {}\".format(eval_metrics))\n",
    "\n",
    "  # Extract accuracy from eval metrics\n",
    "  accuracy = eval_metrics[\"accuracy\"][0]\n",
    "\n",
    "  accuracy_improvement = accuracy - old_accuracy\n",
    "  old_accuracy = accuracy\n",
    "\n",
    "  if accuracy_improvement > 0.01:\n",
    "    # Add high confidence examples to labeled from unlabeled\n",
    "    print(\"Adding high confidence examples to labeled.\")\n",
    "    add_high_confidence_examples_to_labeled(\n",
    "      dataset_id, high_confidence_features_label_query)\n",
    "\n",
    "    # Remove high confidence examples from unlabeled\n",
    "    print(\"Removing high confidence examples from unlabeled.\")\n",
    "    remove_high_confidence_examples_from_unlabeled(\n",
    "      dataset_id, low_confidence_features_query)\n",
    "    \n",
    "    iteration += 1\n",
    "  else:\n",
    "    print(\"Not enough improvement, breaking loop!\")\n",
    "    break"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.5.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
